-- @owner: @nanyang12
-- @date: 2025/3/25
-- @testpoint:GMS_XMLGEN.NEWCONTEXTFROMHIERARCHY语句为层次查询，部分测试点合理报错
@conn PrimaryDbAmode;
--前置条件：
drop extension if exists gms_xmlgen cascade;
create extension gms_xmlgen;
drop extension if exists gms_output cascade;
create extension gms_output;
select gms_output.enable(100000);
--step1:建表
drop table if exists t_new0004 cascade;
create table t_new0004 (
 "integer" integer,
 "float" float,
 "numeric" numeric(20, 6),
 "boolean" boolean,
 "char" char(20),
 "varchar" varchar(20),
 "text" text,
 "blob" blob,
 "raw" raw,
 "date" date,
 "time" time,
 "timestamp" timestamp,
 "json" json,
 "varchar_array" varchar(20)[],
 "money" money,
 "bit" bit(3)
);
insert into t_new0004
values(
 1,
 1.23456,
 1.234567,
 true,
 '"''<>&char test',
 'varchar"''<>&test',
 'text test"''<>&',
 'ff',
 hextoraw('ABCD'),
 '2024-01-02',
 '18:01:02',
 '2024-02-03 19:03:04',
 '{"a" : 1, "b" : 2}',
 array['abc', '"''<>&', '你好'],
 '12.34'::float8::numeric::money,
 B'101'
 ),
 (
 2,
 2.23456,
 2.234567,
 false,
 '2"''<>&char test',
 '2varchar"''<>&test',
 '2text test"''<>&',
 'eeee',
 hextoraw('ffff'),
 '2026-03-04',
 '20:12:13',
 '2026-05-06 21:13:00',
 '[9,8,7,6]',
 array['&^%@', '"''<>&', '<&y''">'],
 '25.59',
 B'101'
 ),
 (
 null,
 null,
 null,
 null,
 null,
 null,
 null,
 null,
 null,
 null,
 null,
 null,
 null,
 null,
 null,
 null
 );

--step2:成功
DECLARE
   xml_output clob;
   xml_cxt_from_hierarchy gms_xmlgen.ctxhandle;
BEGIN
   xml_cxt_from_hierarchy := gms_xmlgen.newcontextfromhierarchy('
   SELECT "integer", xmltype(gms_xmlgen.getxml(''select * from t_new0004''))
   FROM t_new0004
   START WITH "integer" = 1 OR "integer" = 2
   CONNECT BY nocycle "integer" = PRIOR "integer"');
   xml_output := gms_xmlgen.getxml(xml_cxt_from_hierarchy);
   gms_output.put_line(xml_output);
   gms_xmlgen.closecontext(xml_cxt_from_hierarchy);
END;
/

create table t_new0004_02(a int);
insert into t_new0004_02 values(1);
DECLARE
   xml_output clob;
   xml_cxt_from_hierarchy gms_xmlgen.ctxhandle;
BEGIN
   xml_cxt_from_hierarchy := gms_xmlgen.newcontextfromhierarchy('
   SELECT a, xmltype(gms_xmlgen.getxml(''select * from t_new0004_02''))
   FROM t_new0004_02');
   xml_output := gms_xmlgen.getxml(xml_cxt_from_hierarchy);
   gms_output.put_line(xml_output);
   gms_xmlgen.closecontext(xml_cxt_from_hierarchy);
END;
/

insert into t_new0004_02 values(null);
--step3:报错
DECLARE
   xml_output clob;
   xml_cxt_from_hierarchy gms_xmlgen.ctxhandle;
BEGIN
   xml_cxt_from_hierarchy := gms_xmlgen.newcontextfromhierarchy('
   SELECT a, xmltype(gms_xmlgen.getxml(''select * from t_new0004_02''))
   FROM t_new0004_02');
   xml_output := gms_xmlgen.getxml(xml_cxt_from_hierarchy);
   gms_output.put_line(xml_output);
   gms_xmlgen.closecontext(xml_cxt_from_hierarchy);
END;
/

--step4:清理环境
drop table if exists t_new0004 cascade;
drop table if exists t_new0004_02 cascade;
drop extension if exists gms_xmlgen cascade;
drop extension if exists gms_output cascade;